1. Field of the Invention
This invention relates in general to database management systems performed by computers, and in particular, to the optimization of joined table expressions by query transformation in database management systems.
2. Description of Related Art
Computer systems incorporating Relational DataBase Management System (RDBMS) software using a Structured Query Language (SQL) interface are well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American Nationals Standard Institute (ANSI) and the International Standards Organization (ISO).
There are a number of well-known and widely-applied optimization methods for queries available in the prior art. Some examples of these optimization methods include Dynamic Programming, Greedy Algorithm, etc. Generally, these optimization methods attempt to find the best execution strategy for a query based on a cost estimation over a range of possible execution strategies in the search space.
On the other hand, optimization has not been successfully applied to joined table expressions. Joined table expressions are found in FROM clauses of SELECT statements, and generally take the form of xe2x80x9ctable-reference join-operator table-reference ON join-condition.xe2x80x9d The join-operator can be INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN, the table-reference represents a base table or intermediate result table, and the join-condition is a boolean expression that results in a true, false, or unknown value. More complex joined table expressions can be a sequence of simple joined table expressions combined by join operators, parentheses, and nested table expressions.
There is a need in the art for improved methods of optimizing joined table expressions and complex join operations. Specifically, there is a need in the art for improved methods of optimizing joined table expressions and complex join operations by query transformations.
To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, and article of manufacture for optimizing queries, wherein the query is analyzed and a sequence of transformations is triggered to achieve the optimal performance for joined table expressions therein. The transformations include predicates push down for joined table expressions and non-null predicates push down to simplify join operators for joined table expressions.